﻿using Dapper;
using Domain.Entity.System_dictionary;
using Domain.Entity.System_operation;
using Domain.Entity.System_Ordermanagement;
using Domain.IRepository.System_operation;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Volo.Abp.DependencyInjection;
using Volo.Abp.Domain.Entities;

namespace EFCore.Repository.System_operation
{
    [Dependency(ServiceLifetime.Scoped)]
    public class Wb_meterRepository : IWb_meterRepository
    {
        private MyDbContext myDbContext { get; set; }

        public Wb_meterRepository(MyDbContext myDbContext)
        {
            this.myDbContext = myDbContext;
        }

        public int Count(string Origin_place, string Det_place, string tran_manager, string Wb_number)
        {
            string sql = "select * from [dbo].[Wb_meter] where 1=1";
            if (Origin_place != null && Origin_place != "")
            {
                sql += $@" and Origin_place ='{Origin_place}'";
            }
            if (Det_place != null && Det_place != "")
            {
                sql += $@" and Det_place ='{Det_place}'";
            }
            if (tran_manager != null && tran_manager != "")
            {
                sql += $@" and Tran_manager='{tran_manager}'";
            }
            if (Wb_number != null && Wb_number != "")
            {
                sql += $@" and Wb_number like '%{Wb_number}%'";
            }
            //int conut=myDbContext.Database.GetDbConnection().Query<Orders>(sql).Count();
            int count = myDbContext.Database.GetDbConnection().Query(sql).Count();
            return count;
        }

        public List<Wb_meter> GetWb_meter(int currentPage, int PageSize)
        {
            List<Wb_meter> list = myDbContext.Wb_meter.ToList();
            var wb_meterList = list.Skip((currentPage - 1) * PageSize).Take(PageSize).ToList();
            return wb_meterList;
        }

        public List<Wb_meter> GetWb_Meters(string Origin_place, string Det_place, string tran_manager, string Wb_number, int currentPage, int PageSize)
        {
            string sql = $@"select * from [dbo].[Wb_meter] where 1=1";
            if (Origin_place != null && Origin_place != "")
            {
                sql += $@" and Origin_place ='{Origin_place}'";
            }
            if (Det_place != null && Det_place != "")
            {
                sql += $@" and Det_place ='{Det_place}'";
            }
            if (tran_manager != null && tran_manager != "")
            {
                sql += $@" and Tran_manager='{tran_manager}'";
            }
            if (Wb_number != null && Wb_number != "")
            {
                sql += $@" and Wb_number like '%{Wb_number}%'";
            }
            List<Wb_meter> list = myDbContext.Database.GetDbConnection().Query<Wb_meter>(sql).ToList();
            return list.Skip((currentPage - 1) * PageSize).Take(PageSize).ToList();
        }

        public int Delete(int Wb_id)
        {
            string sql = $@"delete from [dbo].[Wb_meter] where Wb_id='{Wb_id}'";
            return myDbContext.Database.GetDbConnection().Execute(sql);
        }

        public List<Wb_meter> GetWb_Meters_wb_id(int Wb_id)
        {
            string sql = $@"select * from [dbo].[Wb_meter] where [Wb_id]='{Wb_id}'";
            return myDbContext.Database.GetDbConnection().Query<Wb_meter>(sql).ToList();
        }

        public int Update(Wb_meter wb_Meter)
        {
            string sql = $@"UPDATE [dbo].[Wb_meter]
                            SET [Wb_number] = '{wb_Meter.Wb_number}',[Wb_status] = '{wb_Meter.Wb_status}',[Origin_place] = '{wb_Meter.Origin_place}',[Det_place] = '{wb_Meter.Det_place}',[Tran_manager] = '{wb_Meter.Tran_manager}',
		                    [Order_time] = '{wb_Meter.Order_time}',[Create_time] = '{wb_Meter.Create_time}',[Create_by] = '{wb_Meter.Create_by}',[Last_time] = '{wb_Meter.Last_time}',[Last_by] = '{wb_Meter.Last_by}',
		                    [Plan_time] = '{wb_Meter.Plan_time}',[Reality_time] = '{wb_Meter.Reality_time}',[Planget_time] = '{wb_Meter.Planget_time}', [Realityget_time] = '{wb_Meter.Realityget_time}', 
		                    [Audit_status] = '{wb_Meter.Audit_status}',[Payable_status] = '{wb_Meter.Payable_status}',[Dv_id] = '{wb_Meter.Dv_id}',[Ord_id] = '{wb_Meter.Ord_id}',[Cr_id] = '{wb_Meter.Cr_id}',
		                    [Vc_id] = '{wb_Meter.Vc_id}',[Bill_method] = '{wb_Meter.Bill_method}',[Freight_costs] = '{wb_Meter.Freight_costs}',[Packag_fee] = '{wb_Meter.Packag_fee}',[Other_fee] = '{wb_Meter.Other_fee}',
		                    [Freight_count] = '{wb_Meter.Freight_count}',[Pay_as_you_go] = '{wb_Meter.Pay_as_you_go}',[Prepayment] = '{wb_Meter.Prepayment}',[Collect] = '{wb_Meter.Collect}',
		                    [Periodical_payment] = '{wb_Meter.Periodical_payment}',[Pay_count] = '{wb_Meter.Pay_count}',[Handling_fee] = '{wb_Meter.Handling_fee}',[Vc_model] = '{wb_Meter.Vc_model}',
		                    [Vc_length] = '{wb_Meter.Vc_length}'
                            WHERE [Wb_id]='{wb_Meter.Wb_id}';";
            return myDbContext.Database.GetDbConnection().Execute(sql);
        }

        public List<Sys_dict_data> Get_Dict_Datas(string Dict_label)
        {
            string sql = $@"select * from [dbo].[Sys_dict_data] where [Dict_label]='{Dict_label}'";
            return myDbContext.Database.GetDbConnection().Query<Sys_dict_data>(sql).ToList();
        }

        public List<Wb_meter> GetWb_Meters_status(string wb_status, int currentPage, int PageSize)
        {
            string sql = $@"select * from [dbo].[Wb_meter] where Wb_status='{wb_status}'";
            List<Wb_meter> list = myDbContext.Database.GetDbConnection().Query<Wb_meter>(sql).ToList();
            return list.Skip((currentPage - 1) * PageSize).Take(PageSize).ToList();
        }

        public int Count_status(string wb_status)
        {
            string sql = $@"select * from [dbo].[Wb_meter] where Wb_status='{wb_status}'";
            return myDbContext.Database.GetDbConnection().Query(sql).Count();
        }

        public int Add_Bill_ol(Bill_ol bill_Ol)
        {
            string sql = $@"INSERT INTO [dbo].[Bill_ol]
                            ([Wb_Id], [Customer], [Bill_Status], [Bill_Id], [Wb_number], [Order_Id], 
                             [Origin_place], [Det_place], [Pickup_address], [Delivery_address], [Driver_name], [Car_number], [Driver_phone], 
                             [Bill_nums], [Bill_volume], [Bill_weight], [Bill_cost], [Pickup_time], [Pickup_endtime], [Plan_time], 
                             [Reality_time], [PlanGet_time], [RealityGet_time], [Del_flag], [Create_time], [Create_by])
                             VALUES
                            ('{bill_Ol.Wb_Id}', '{bill_Ol.Customer}', '{bill_Ol.Bill_Status}', '{bill_Ol.Bill_Id}', '{bill_Ol.Wb_number}', 
                            '{bill_Ol.Order_Id}', '{bill_Ol.Origin_place}', '{bill_Ol.Det_place}', '{bill_Ol.Pickup_address}', 
                            '{bill_Ol.Delivery_address}', '{bill_Ol.Driver_name}', '{bill_Ol.Car_number}', '{bill_Ol.Driver_phone}', 
                            '{bill_Ol.Bill_nums}', '{bill_Ol.Bill_volume}', '{bill_Ol.Bill_weight}', '{bill_Ol.Bill_cost}', 
                            '{bill_Ol.Pickup_time}', '{bill_Ol.Pickup_endtime}', '{bill_Ol.Plan_time}', '{bill_Ol.Reality_time}', 
                            '{bill_Ol.PlanGet_time}', '{bill_Ol.RealityGet_time}', '{bill_Ol.Del_flag}', 
                            '{bill_Ol.Create_time}', '{bill_Ol.Create_by}')";
            return myDbContext.Database.GetDbConnection().Execute(sql);
        }

        public List<Orders> GetOrders(int Ord_id)
        {
            string sql = $@"select * from [dbo].[Orders] where Ord_id='{Ord_id}'";
            return myDbContext.Database.GetDbConnection().Query<Orders>(sql).ToList();
        }

        public int Update_status(int Wb_id)
        {
            string sql = $@"update [dbo].[Wb_meter] set [Audit_status]='1' where [Wb_id]='{Wb_id}'";
            return myDbContext.Database.GetDbConnection().Execute(sql);
        }
    }
}
